﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WpfSnqkGasAnalysis.Model;
using System.Collections.Concurrent;

namespace WpfSnqkGasAnalysis.IData_impl
{
    /// <summary>
    /// 基础类，提供公有方法
    /// </summary>
    /// 创建时间：2022-11-23 14:36:17。作者：王浩力
    public class BaseDAL
    {
        /// <summary>
        /// 写入数据检查并发，不允许并发写。
        /// 防止数据库被锁抛出database is locked异常
        /// </summary>
        /// 创建时间：2022-12-1 17:07:06。作者：王浩力
        private static ConcurrentDictionary<string, bool> insertCheck = new ConcurrentDictionary<string, bool>();

        /// <summary>
        /// 等待解锁时长，毫秒
        /// </summary>
        const int KONG_SECONDS = 10;

        /// <summary>
        /// (同步执行)执行原始sql语句，有返回值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">原始sql语句</param>
        /// <param name="paramesDict">参数</param>
        /// <param name="action">委托，有返回值</param>
        /// <param name="exceptionAction">异常消息委托，调用者处理消息，比如存起来</param>
        /// <returns></returns>
        public virtual T ExecSqlCmd<T>(string sql, Dictionary<string, object> paramesDict, Func<DbCommand, T> action, Action<Exception> exceptionAction = null)
        {
            //获取操作锁
            LockCheck(sql);

            bool haveEdit = false;
            if (sql.IndexOf("insert", StringComparison.OrdinalIgnoreCase) >= 0
              || sql.IndexOf("delete", StringComparison.OrdinalIgnoreCase) >= 0
              || sql.IndexOf("update", StringComparison.OrdinalIgnoreCase) >= 0
              || sql.IndexOf("alter", StringComparison.OrdinalIgnoreCase) >= 0
             )
            {
                haveEdit = true;
            }

            System.Data.SQLite.SQLiteConnection connection = null;
            SQLiteCommand command = null;
            SQLiteTransaction transaction = null;
            try
            {
                connection = new System.Data.SQLite.SQLiteConnection(CommonConfig.ConnectionStr);
                connection.Open();
                if (haveEdit)
                {
                    transaction = connection.BeginTransaction();
                }

                command = connection.CreateCommand();
                command.CommandText = sql;

                if (paramesDict?.Count > 0)
                {
                    foreach (var item in paramesDict)
                    {
                        command.Parameters.AddWithValue(item.Key, item.Value);
                    }
                }

                //调用方实现自己的逻辑，比如读取字段数据封装到实体
                if (action == null)
                {
                    transaction?.Commit();
                    return default;
                }
                T k = action.Invoke(command);
                transaction?.Commit();
                return k;
            }
            catch (Exception ex)
            {
                transaction?.Rollback();

                var ex1 = ex.InnerException ?? ex;
                var ex2 = ex1.InnerException ?? ex1;
                LogHelpter.AddLog("数据库执行异常，" + ex2.Message + "。执行的sql:" + sql, null, "db_error");

                //异常消息返回给调用方处理。创建时间：2022-12-1 16:54:31
                exceptionAction?.Invoke(ex2);
            }
            finally
            {
                transaction?.Dispose();

                if (command != null)
                {
                    command.Dispose();
                }
                if (connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }

                insertCheck.Clear();//解锁
            }
            return default;
        }

        /// <summary>
        /// (同步执行)执行原始sql语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">原始sql语句</param>
        /// <param name="paramesDict">参数</param>
        /// <param name="action">委托</param>
        /// <param name="exceptionAction">异常消息委托，调用者处理消息，比如存起来</param>
        /// <returns></returns>
        public virtual void ExecSqlCmd(string sql, Dictionary<string, object> paramesDict, Action<DbCommand> action, Action<Exception> exceptionAction = null)
        {
            LockCheck(sql);

            System.Data.SQLite.SQLiteConnection connection = null;
            SQLiteCommand command = null;
            try
            {
                connection = new System.Data.SQLite.SQLiteConnection(CommonConfig.ConnectionStr);
                connection.Open();
                command = connection.CreateCommand();
                command.CommandText = sql;

                if (paramesDict?.Count > 0)
                {
                    foreach (var item in paramesDict)
                    {
                        command.Parameters.AddWithValue(item.Key, item.Value);
                    }
                }

                //调用方实现自己的逻辑，比如读取字段数据封装到实体
                action?.Invoke(command);
            }
            catch (Exception ex)
            {
                var ex1 = ex.InnerException ?? ex;
                var ex2 = ex1.InnerException ?? ex1;
                LogHelpter.AddLog("数据库执行异常，" + ex2.Message + "。执行的sql:" + sql, null, "db_error");

                //异常消息返回给调用方处理。创建时间：2022-12-1 16:54:31
                exceptionAction?.Invoke(ex2);
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                }
                if (connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }

                insertCheck.Clear();//解锁
            }
        }
         
        /// <summary>
        /// 获取操作锁，同一时间只允许一个操作执行
        /// </summary>
        private static void LockCheck(string sql)
        {
            //写入数据并发检查。否则会导致数据库锁定
            if (sql.IndexOf("insert", StringComparison.OrdinalIgnoreCase) >= 0
                || sql.IndexOf("delete", StringComparison.OrdinalIgnoreCase) >= 0
                 || sql.IndexOf("update", StringComparison.OrdinalIgnoreCase) >= 0
                 || sql.IndexOf("alter", StringComparison.OrdinalIgnoreCase) >= 0
                )
            {
                while (insertCheck.Count > 0)
                {
                    System.Threading.Thread.Sleep(KONG_SECONDS);
                    //System.Threading.Thread.SpinWait(KONG_SECONDS);
                    //Task.Delay(KONG_SECONDS).Wait();
                }
                insertCheck.TryAdd("insert", true);
            }

            ////只允许一个操作
            //while (insertCheck.Count > 0)
            //{
            //    LogHelpter.AddLog("数据库正忙...");
            //    System.Threading.Thread.Sleep(KONG_SECONDS);
            //    //System.Threading.Thread.SpinWait(KONG_SECONDS);
            //    //Task.Delay(KONG_SECONDS).Wait();
            //}

            //LogHelpter.AddLog("拿取到DB操作权限，" + sql);

            //insertCheck.TryAdd("112", true);
        }

          

    }
}
